﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace MuaDeal
{
    class Datahelper
    {
        //private SqlConnection connection = new SqlConnection("server=.; database=HQT03_MUADEAL; User ID=DANG NHUNG; Pwd=123456; Connect Timeout=40");
        private SqlConnection connection = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=D:\\KHOA\\KH2_HC\\HE_QTCSDL\\Do an\\subhqtkhtn\\MuaDeal\\HQT03_MUADEAL.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");

        public SqlConnection Connection
        {
            get { return connection; }
            set { connection = value; }
        }

        //Mở kết nối
        public void Open()
        {
            Connection.Open();
        }

        //Đóng kết nối
        public void Close()
        {
            Connection.Close();
        }

        //Thiết lập kết nối(test kết nối)
        public int ThietLapKetNoi()
        {
            
            try
            {
                Connection.Open();
                Connection.Close();
                return 1;
            }
            catch (System.Exception es)
            {
                return 0;
            }
        }

        //ExecuteQuery với câu lệnh sql
        public DataTable ExecuteQuery(String sql)
        { 
            DataTable dt = new DataTable();
            try
            {
                //XÂY DỰNG CÂU TRUY VẤN
                SqlCommand cmd = new SqlCommand(sql,connection);
                //LẤY DỮ LIỆU SAU KHI THỰC HIỆN CÂU TRUY VẤN
                SqlDataAdapter adt = new SqlDataAdapter(cmd);
                adt.Fill(dt);
            }
            catch (System.Exception e)
            {
            }
                //Hiển thị dữ liệu lên 
                return dt;
        }

        //ExecuteQuery với Store
        public DataTable ExecuteQueryStore(String proc)
        {
            DataTable dt = new DataTable();
            try
            {
                SqlCommand cmd = new SqlCommand(proc, connection);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter dta = new SqlDataAdapter(cmd);
                dta.Fill(dt);
                return dt;
            }
            catch (System.Exception e)
            {
            }
            return dt;
        }

    }
}
